
#include "StdAfx.h"
#include "FMDBCommand.h"
#include "FMErrorCheck.h"
#include "oci.h"
#include <vector>
#include "FMDBParameter.h"
#include "FMOCIConstDefine.h"

FMDBCommand::FMDBCommand(void)
{
	FMDBCommand(NULL);
}

FMDBCommand::FMDBCommand( FMDBConnection *connection )
{
	m_strSQL=_T("");
	m_pOCIErr=NULL;
	m_Params=NULL;
	SetConnection(connection);//use connection method to make sure the initialization do correct

}
FMDBCommand::~FMDBCommand(void)
{
}

/// <summary>
/// execute a DML SQL statement, do not query any data back
/// </summary>
void FMDBCommand::ExecuteSQLNoQuery( CString strSQL,FMDBParameters *Params/*=NULL*/,ub4 ExecuteMode/*=EXECUTION_MODE_DEFAULT*/)
{
	//caller override SQL statement and binding parameters
	if (!strSQL.IsEmpty())
	{
		m_strSQL=strSQL;
	}
	if (Params)
	{
		m_Params=Params;
	}


	OCIBind **pbind=NULL;//bind handle pointer array
	OCIBind **pIOVbind=NULL;//bind handle pointer array
	OCIBind **pCursorbind=NULL;//bind handle pointer array

	OCIStmt *pStmt=NULL;

	//get statement handle from prepared
	CheckErr(m_pOCIErr,OCIStmtPrepare2(m_conn->GetHandle(),
										&pStmt,															//returned statement handler
										m_pOCIErr,														//error handler(I use global error handler)
										(const text *)m_strSQL.GetBuffer(),(ub4)_tcslen(m_strSQL.GetBuffer())*sizeof(TCHAR),	//SQL statement text
										NULL,0,															//tagging parameters
										OCI_NTV_SYNTAX,OCI_DEFAULT));									//mode

	ub4 num_iterations=1;//default value

	if(m_Params)//if binding parameters exist
	{
		//parameter binding
		int i=0;
		std::vector<FMDBParameter *> vTemp=m_Params->GetParameters();
		if (vTemp.size()>0)
		{
			pbind=new OCIBind*[m_Params->GetParameters().size()];
			for (std::vector<FMDBParameter *>::iterator iter=vTemp.begin();iter!=vTemp.end();iter++)
			{
				CheckErr(m_pOCIErr,OCIBindByName(pStmt,&(pbind[i]),m_pOCIErr,
					(text *) ((*iter)->GetVarName()),-1,//place holder name and size
					(void *) ((*iter)->GetVarValue()),	//address of program variable
					DBTOOLS::GetSizeFromOraDataType((*iter)->GetVarOraDataType(),(*iter)->GetVarValue()),//size of program variable
					(*iter)->GetVarOraDataType(),NULL,	//type code of the program variable
					(ub2 *)NULL,(ub2 *)NULL,
					0,(ub4 *)NULL,
					OCI_DEFAULT));

				i++;
			}
			num_iterations=m_ValueArraySize;
		}
		
		//bind IOV parameters
		i=0;
		std::vector<FMDBIOVParameter *> vIOVTemp=m_Params->GetIOVParameters();
		if (vIOVTemp.size()>0)
		{
			pIOVbind=new OCIBind*[m_Params->GetIOVParameters().size()];
			for (std::vector<FMDBIOVParameter *>::iterator iter=vIOVTemp.begin();iter!=vIOVTemp.end();iter++)
			{
				CheckErr(m_pOCIErr,OCIBindByName(pStmt,&(pIOVbind[i]),m_pOCIErr,
					(text *) ((*iter)->GetVarName()),-1,//place holder name and size
					(void *) ((*iter)->GetVarValue()),	//address of program variable
					((OCIIOV *)(*iter)->GetVarValue())->bfl,//size of program variable
					(*iter)->GetVarOraDataType(),NULL,	//type code of the program variable
					(ub2 *)NULL,(ub2 *)NULL,
					0,(ub4 *)NULL,
					OCI_IOV));

				i++;
			}
			num_iterations=(*m_Params->GetIOVParameters().begin())->GetVarArraySize();
		}
		
		//bind Cursor parameter
		i=0;
		OCIStmt **pstmtTmp=NULL;//output cursor pointer array
		std::vector<FMDBCursorParameter *> vCursorTemp=m_Params->GetCursorParameters();
		if (vCursorTemp.size()>0)
		{
			pCursorbind=new OCIBind*[m_Params->GetCursorParameters().size()];
			pstmtTmp=new OCIStmt*[m_Params->GetCursorParameters().size()];//do not release, the parameter will do
			for (std::vector<FMDBCursorParameter *>::iterator iter=vCursorTemp.begin();iter!=vCursorTemp.end();iter++)
			{
				//allocate statement handle				
				CheckErr(m_pOCIErr,OCIHandleAlloc(m_conn->GetFMDBApp()->GetOCIEnv(),
					(void **)&(pstmtTmp[i]),
					OCI_HTYPE_STMT,
					(size_t)0,
					(void **)NULL));

				(*iter)->SetVarValue(pstmtTmp[i]);

				//bind cursor
				CheckErr(m_pOCIErr,OCIBindByName(pStmt,&(pCursorbind[i]),m_pOCIErr,
					(text *) ((*iter)->GetVarName()),-1,//place holder name and size
					(void *)&(pstmtTmp[i]),	//address of program variable
					DBTOOLS::GetSizeFromOraDataType((*iter)->GetVarOraDataType(),(*iter)->GetVarValue()),//size of program variable
					(*iter)->GetVarOraDataType(),NULL,	//type code of the program variable
					(ub2 *)NULL,(ub2 *)NULL,
					0,(ub4 *)NULL,
					OCI_DEFAULT));

				i++;
			}
			num_iterations=1;//note:when parameter include cursor,then only run once
		}
	}

	//todo:add BLOB parameter binding
	//OCIDescriptorAlloc
	//OCILobCreateTemporary

	//execute statement
	//todo:just for test ,add transaction and do not commit 
	CheckErr(m_pOCIErr,OCIStmtExecute(m_conn->GetHandle(),pStmt,m_pOCIErr,num_iterations,0,(OCISnapshot *)NULL,(OCISnapshot *)NULL,ExecuteMode));

	//define stmt for cursor
	std::vector<FMDBCursorParameter *> vCursorTemp=m_Params->GetCursorParameters();
	if (vCursorTemp.size()>0)
	{
		for (std::vector<FMDBCursorParameter *>::iterator iterCursor=vCursorTemp.begin();iterCursor!=vCursorTemp.end();iterCursor++)
		{
			if ((*iterCursor)->GetRecordset())
			{
				OCIDefine **pdefine=NULL;//define handle pointer array
				OCIDefine **pIOVdefine=NULL;//define handle pointer array

				//set recordset error handle as command error handle
				(*iterCursor)->GetRecordset()->SetErrorHandle(m_pOCIErr);
				(*iterCursor)->GetRecordset()->SetStatementHandle((OCIStmt*)(*iterCursor)->GetVarValue(),false);//note: here we set isRelease=false because the stmt is allocate by ocihandlealloc function
				if (ExecuteMode==EXECUTION_MODE_SCROLLABLE_READONLY)
				{
					(*iterCursor)->GetRecordset()->IsScrollable(true);//note: cursor as define is non-scrollable cursor, scrollable cursor reduce performance
				}
				else
				{
					(*iterCursor)->GetRecordset()->IsScrollable(false);
				}

				
				//here we notice recordset the statement is ready, recordset can do things depend on statement,for example:
				//FMDBRecordSetAuto object can create columns and storage
				(*iterCursor)->GetRecordset()->IsReady(true);


				/*ub4 ColCount=(*iterCursor)->GetRecordset()->GetColumnCount();
				if (ColCount>0)
				{
					pdefine=new OCIDefine*[ColCount];					
				}*/





				std::map<ub4,FMDBColumn*> *pCols=(*iterCursor)->GetRecordset()->GetColumns();
				if (pCols->size()>0)
				{
					pdefine=new OCIDefine*[pCols->size()];
					int j=0;
					for (std::map<ub4,FMDBColumn*>::const_iterator iterCol=pCols->begin();iterCol!=pCols->end();iterCol++)
					{
						if (iterCol->second!=NULL)
						{
							CheckErr(m_pOCIErr,OCIDefineByPos((OCIStmt*)(*iterCursor)->GetVarValue(),&(pdefine[j]),m_pOCIErr,iterCol->second->GetColumnId(),
								iterCol->second->GetStorage(),DBTOOLS::GetSizeFromOraDataType(iterCol->second->GetType(),iterCol->second->GetStorage()),
								iterCol->second->GetType(),
								iterCol->second->GetIndicator(),
								NULL,NULL,OCI_DEFAULT));

							j++;
						}
					}
				}

				//define IOV columns
				std::map<ub4,FMDBIOVColumn*> *pIOVCols=(*iterCursor)->GetRecordset()->GetIOVColumns();
				if (pIOVCols->size()>0)
				{
					int j=0;
					pIOVdefine=new OCIDefine*[pCols->size()];
					for (std::map<ub4,FMDBIOVColumn*>::iterator iterCol=pIOVCols->begin();iterCol!=pIOVCols->end();iterCol++)
					{
						if (iterCol->second!=NULL)
						{
							CheckErr(m_pOCIErr,OCIDefineByPos((OCIStmt*)(*iterCursor)->GetVarValue(),&(pIOVdefine[j]),m_pOCIErr,iterCol->second->GetColumnId(),
								iterCol->second->GetStorage(),iterCol->second->GetStorage()[0].bfl,
								iterCol->second->GetType(),
								iterCol->second->GetIndicator(),
								NULL,NULL,OCI_IOV));

							j++;
						}
					}
				}
			}
		}
	}

	
	//release the statement handle
	CheckErr(m_pOCIErr,OCIStmtRelease(pStmt,m_pOCIErr,(OraText *)NULL,0,OCI_DEFAULT));

}

void FMDBCommand::Close()
{
	//todo:release all resource
}

//this function is used for repeatedly executing a SQL statement, do not support variable binding,
//if you want to use variable binding, use ExecuteSQLNoQuery function instead
void FMDBCommand::ExecuteSQLNoQueryRepeatly( CString strSQL,int RepeatTimes/*=1*/,ub4 ExecuteMode/*=EXECUTION_MODE_COMMIT_ON_SUCCESS*/ )
{
	OCIStmt *pStmt=NULL;

	//get statement handle from prepared
	CheckErr(m_pOCIErr,OCIStmtPrepare2(m_conn->GetHandle(),
		&pStmt,												//returned statement handler
		m_pOCIErr,											//error handler(I use global error handler)
		(const text *)strSQL.GetBuffer(),(ub4)_tcslen(strSQL.GetBuffer())*sizeof(TCHAR),	//SQL statement text
		NULL,0,												//tagging parameters
		OCI_NTV_SYNTAX,OCI_DEFAULT));						//mode

	ub4 num_iterations=RepeatTimes;

	//execute statement
	//todo:just for test ,add transaction and do not commit 
	CheckErr(m_pOCIErr,OCIStmtExecute(m_conn->GetHandle(),pStmt,m_pOCIErr,num_iterations,0,(OCISnapshot *)NULL,(OCISnapshot *)NULL,ExecuteMode));


	//release the statement handle
	CheckErr(m_pOCIErr,OCIStmtRelease(pStmt,m_pOCIErr,(OraText *)NULL,0,OCI_DEFAULT));

}

//execute SQL statement and get value
//note:record set must be initialized outside and predefined data structure.
void FMDBCommand::ExecuteSQL( FMDBRecordSet *rs,CString strSQL,FMDBParameters *Params/*=NULL*/,ub4 ExecuteMode/*=DEFAULT*/)
{
	//record set must exists
	if(!rs)
	{
		return;
	}

	//caller override SQL statement and binding parameters
	if (!strSQL.IsEmpty())
	{
		m_strSQL=strSQL;
	}
	if (Params)
	{
		m_Params=Params;
	}

	//set recordset error handle as command error handle
	rs->SetErrorHandle(m_pOCIErr);

	OCIBind **pbind=NULL;//bind handle pointer array
	OCIDefine **pdefine=NULL;//define handle pointer array
	OCIDefine **pIOVdefine=NULL;//define handle pointer array
	OCIStmt *pStmt=NULL;

	//get statement handle from prepared
	CheckErr(m_pOCIErr,OCIStmtPrepare2(m_conn->GetHandle(),
		&pStmt,															//returned statement handler
		m_pOCIErr,														//error handler(I use global error handler)
		(const text *)m_strSQL.GetBuffer(),(ub4)_tcslen(m_strSQL.GetBuffer())* sizeof(TCHAR),	//SQL statement text
		NULL,0,															//tagging parameters
		OCI_NTV_SYNTAX,OCI_DEFAULT));									//mode

	rs->SetStatementHandle(pStmt);


	if(m_Params)//if binding parameters exist
	{
		//parameter binding
		int i=0;
		std::vector<FMDBParameter *> vTemp=m_Params->GetParameters();
		if (vTemp.size()>0)
		{
			pbind=new OCIBind*[m_Params->GetParameters().size()];
			for (std::vector<FMDBParameter *>::iterator iter=vTemp.begin();iter!=vTemp.end();iter++)
			{
				CheckErr(m_pOCIErr,OCIBindByName(pStmt,&(pbind[i]),m_pOCIErr,
					(text *) ((*iter)->GetVarName()),-1,//place holder name and size
					(void *) ((*iter)->GetVarValue()),	//address of program variable
					DBTOOLS::GetSizeFromOraDataType((*iter)->GetVarOraDataType(),(*iter)->GetVarValue()),//size of program variable
					(*iter)->GetVarOraDataType(),//type code of the program variable
					NULL,	//indicator
					(ub2 *)NULL,(ub2 *)NULL,
					0,(ub4 *)NULL,
					OCI_DEFAULT));

				i++;
			}
		}

		//note:select SQL statement do not support(do not need) IOV parameters?
	}

	//execute statement
	//note:we use scrollable statement. if we use non-scrollable statement we should add choice here
	CheckErr(m_pOCIErr,OCIStmtExecute(m_conn->GetHandle(),pStmt,m_pOCIErr,0,0,(OCISnapshot *)NULL,(OCISnapshot *)NULL,ExecuteMode ));
	//note:here we do not release statement handle, leave it to FMDBRecordset do.

	if (ExecuteMode==EXECUTION_MODE_SCROLLABLE_READONLY)
	{
		rs->IsScrollable(true);//note: cursor as define is non-scrollable cursor, scrollable cursor reduce performance
	}
	else
	{
		rs->IsScrollable(false);
	}

	
	//note:here we use after define, it's loss some performance but it's good for FMDBRecordSetAuto to define columns automatically
	rs->IsReady(true);//here we notice recordset the statement is ready, recordset can do things depend on statement
	//define columns
	std::map<ub4,FMDBColumn*> *pCols=rs->GetColumns();
	if (pCols->size()>0)
	{
		pdefine=new OCIDefine*[pCols->size()];
		int i=0;
		for (std::map<ub4,FMDBColumn*>::const_iterator iter=pCols->begin();iter!=pCols->end();iter++)
		{
			if (iter->second!=NULL)
			{
				CheckErr(m_pOCIErr,OCIDefineByPos(pStmt,&(pdefine[i]),m_pOCIErr,iter->second->GetColumnId(),
					iter->second->GetStorage(),DBTOOLS::GetSizeFromOraDataType(iter->second->GetType(),iter->second->GetStorage()),
					iter->second->GetType(),
					iter->second->GetIndicator(),//indicator
					NULL,NULL,OCI_DEFAULT));

				i++; 
			}
		}
	}

	//define IOV columns
	std::map<ub4,FMDBIOVColumn*> *pIOVCols=rs->GetIOVColumns();
	if (pIOVCols->size()>0)
	{
		int i=0;
		pIOVdefine=new OCIDefine*[pIOVCols->size()];
		for (std::map<ub4,FMDBIOVColumn*>::const_iterator iter=pIOVCols->begin();iter!=pIOVCols->end();iter++)
		{
			if (iter->second!=NULL)
			{
				CheckErr(m_pOCIErr,OCIDefineByPos(pStmt,&(pIOVdefine[i]),m_pOCIErr,iter->second->GetColumnId(),
					iter->second->GetStorage(),iter->second->GetStorage()[0].bfl,
					iter->second->GetType(),
					iter->second->GetIndicator(),
					NULL,NULL,OCI_IOV));

				i++;
			}
		}
	}
}